### 在 goland 中编写,  所以会携带库名
### 库名 beggo

# 创建表
create table beggo.demo(
    id int(11),
    username varchar(255),
    age int(3),
    sex int(1)
);

# 增
insert into beggo.demo(id, username, age, sex) values (1, 'tiger', 18, 0);
insert into beggo.demo(id, username, age, sex) values (2, 'cat', 18, 0);
insert into beggo.demo(id, username, age, sex) values (3, 'lion', 18, 0);

# 改
update beggo.demo set age=20 where id = 1;

# 删
delete from beggo.demo where id = 3;

# 查
select * from beggo.demo;

# 查询某个字段
# select (id, name) from beggo.nav;

# 排序  == > desc 倒叙, asc 升序, 默认升序
select * from beggo.demo order by id desc;
# 均为升序
select * from beggo.demo order by id;
select * from beggo.demo order by id;

# 统计数量
select count(*) from beggo.demo;
# 统计第一列有数据的数量
select count(1) from beggo.demo;
# 可统计某一列
select count(id) from beggo.demo;

# 分页
select * from beggo.demo limit 1 ;
# 查询第二页
select * from beggo.demo limit 1, 2 ;


### where 相关字段
# =, >, <, and , or , >= , <= , !=, is not null , is null, between, in .....

# 模糊查询
# 查询 username 包含 tiger 的数据
select * from beggo.demo where username like '%ti%';

# 左连接
select * from beggo.demo d left join beggo.users u on d.id = u.id;
# 右连接
select * from beggo.demo d right join beggo.users u on d.id = u.id;


# 快速插入数据
insert into beggo.users (`username`) select username from beggo.users;

select count(1) from beggo.users; # 2 变 4194304 ==> 上面一条指令反复增加 是指数级的


# 优化查询
select * from beggo.users where username='lion'; # 6 s 577 ms

# 设置索引
# 还以为这个会有什么神奇内容, 快进了半天只有设置索引
select * from beggo.users where username='lion'; # 67 ms


# 事务 与 lock

# 事务 = > 多个sql 要么同时成功, 要么同时失败
begin;
update beggo.bank set balance = balance - 100 where id = 1;
update beggo.bank set balance = balance + 100 where id = 2;
commit;
# 遇到错误
# rollback 回滚

# 锁 lock

# 表锁 ===> 读锁 只读 不可写
lock tables beggo.users read;
update beggo.users set username = 'dog' where id = 1; # Table 'users' was locked with a READ lock and can't be updated
unlock tables; # 可读可写
select * from beggo.users where username='dog';

# 写锁
# 写锁 其他用户不可读 可写
lock tables beggo.users write;
select * from beggo.users where id = 1; # 如果是其他用户 不可读
update beggo.users set username = 'elephant' where id = 1; # 可写
unlock tables;